package com.kaifamiao.io.d0923;


import com.kaifamiao.jdbc.d0908.entity.Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBHelper {

    @Deprecated
    private static void queryById(int id) throws SQLException{
        try {
            // 1, 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        String url = "jdbc:mysql://localhost:3306/kfm";

        Connection conn = DriverManager.getConnection(url, "root", "");


        String sql = "select * from users where id = ?";
        PreparedStatement statement = conn.prepareStatement(sql);

        statement.setObject(1, id);

        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()) {
            System.out.print(resultSet.getObject(1) + "\t");
            System.out.println(resultSet.getObject(2));
        }

        resultSet.close();
        statement.close();
        conn.close();
    }
    @Deprecated
    private static void queryAll() throws SQLException{
        try {
            // 1, 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        String url = "jdbc:mysql://localhost:3306/kfm";

        Connection conn = DriverManager.getConnection(url, "root", "");

        String sql = "select * from users";
        PreparedStatement statement = conn.prepareStatement(sql);


        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()) {
            System.out.print(resultSet.getObject(1) + "\t");
            System.out.println(resultSet.getObject(2));
        }

        resultSet.close();
        statement.close();
        conn.close();
    }

    static {
        // 只执行一次
        try {
            // 1, 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(String url, String user, String password) throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static ResultSet query(Connection conn, String sql, Object...params) throws SQLException {

        PreparedStatement statement = conn.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }

        return statement.executeQuery();
    }

    // select * from users   --> Users
    // select * from student --> Student
    public static Users queryOne(Connection conn, String sql, Object...params) throws SQLException {

        PreparedStatement statement = conn.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }

        ResultSet set = statement.executeQuery();
        if (set.next()){
            return mapper(set);
        }
        return null;
    }

    public static List<Users> queryMany(Connection conn, String sql, Object...params) throws SQLException {

        PreparedStatement statement = conn.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }

        ResultSet set = statement.executeQuery();
        return mapperAll(set);
    }

    public static Users mapper(ResultSet resultSet) throws SQLException {
        Users users = new Users();
        users.setId(resultSet.getInt("id"));
        users.setUsername(resultSet.getString("username"));
        users.setPassword(resultSet.getString("password"));
//        users.setCreateDate(resultSet.getDate("createDate"));
        users.setGender(resultSet.getInt("gender"));
        return users;
    }

    public static List<Users> mapperAll(ResultSet resultSet) throws SQLException {
        List<Users> list = new ArrayList<>();
        while (resultSet.next()){
            Users users = mapper(resultSet);

            list.add(users);
        }
        return list;
    }

    /*public static void close(){
        resultSet.close();
        statement.close();
        conn.close();
    }*/
}
